import re, datetime
import pymysql
from docx import Document
from docx.document import Document as dc
from docx.table import _Cell, Table
from docx.oxml.text.paragraph import CT_P
from docx.text.paragraph import Paragraph
from docx.oxml.table import CT_Tbl
import random
from datetime import datetime




class ToDataBase:
    def __init__(self):
        self.conn = pymysql.connect(host='192.168.35.221',
                                    user='llm',
                                    password='llm.mysql@147258',
                                    db='llm',
                                    port=3307,
                                    charset='utf8mb4',
                                    cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.conn.cursor()

    def is_exists(self, article_name):
        query = "SELECT txt_id FROM documents_article_info WHERE old_article_name = %s"
        self.cursor.execute(query, (article_name,))
        cf = self.cursor.fetchone()
        if cf:
            return cf['txt_id']
        else:
            return cf

    def insert_article(self, article_name, file_path, author):
        sql = "INSERT INTO  documents_article_info(article_name,old_article_name,file_path,author,submit_time) VALUES ( %s,%s,%s,%s,%s) "
        new_article_name = str(datetime.now().strftime('%Y_%m_%d_%H_%M_%S'))+str(random.randint(1,10000))
        submit_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        self.cursor.execute(sql, (new_article_name, article_name, file_path, author, submit_time))
        self.conn.commit()
        return new_article_name


    def insert_data_to_read_info(self,article_id, read_id, read_text, flag, owner, owner1, owner2, owner3):
        sql = "INSERT INTO documents_read_info (article_id,read_id,read_text,read_flag,owner,owner1,owner2,owner3) VALUES ( %s, %s,%s, %s,%s,%s, %s,%s) "
        self.cursor.execute(sql, (article_id, read_id, read_text, flag, owner, owner1, owner2, owner3))
        self.conn.commit()

    # 将数据插入文件名表中
    def insert_data_to_article_info(self,article_name, file_path, author, submit_time):
        sql = "INSERT INTO  documents_article_info(article_name,file_path,author,submit_time) VALUES ( %s,%s, %s,%s) "
        self.cursor.execute(sql, (article_name, file_path, author, submit_time))
        self.conn.commit()

    # 为每一行增加祖先节点内容
    def insert_data_to_read_info_mid(self,article_id):
        sql = '''
 INSERT INTO documents_read_info_mid (article_id, owner, read_flag ,text_chunk_id, text_with_title)
        SELECT 	%s as article_id
					,owner
					,read_flag
                    ,text_chunk_id
					,CONCAT(
							IFNULL(GROUP_CONCAT(DISTINCT t5.read_text order by t5.read_id SEPARATOR ''),'')
							,' '
							,IFNULL(GROUP_CONCAT(DISTINCT t4.read_text order by t4.read_id SEPARATOR ''),'')
							,' '
							,IFNULL(GROUP_CONCAT(DISTINCT t3.read_text order by t3.read_id SEPARATOR ''),'')
							,' '
							,IFNULL(GROUP_CONCAT(DISTINCT t2.read_text order by t2.read_id SEPARATOR ''),'')
							,' '
							,GROUP_CONCAT(t1.read_text order by t1.read_id SEPARATOR '')
							) as text_with_title 
			from 
			(SELECT  read_id 
					,read_text 
					,read_flag
					,owner 
					,owner1 
					,owner2 
					,owner3 
                    ,FLOOR((sum(CHAR_LENGTH(read_text)) over(PARTITION by article_id,read_flag,owner order by article_id,read_id))/950) text_chunk_id
			from documents_read_info  
			where article_id = %s and read_flag not in (0,1,2,3) 
			) t1
			left join 
			(select read_id 
					,read_text 
			from documents_read_info
			where article_id = %s
			)
			t2 on t1.owner = t2.read_id 
			left join 
			(select read_id 
					,read_text 
			from documents_read_info
			where article_id = %s
			)
			t3 on t1.owner1 = t3.read_id 
			left join 
			(select read_id 
					,read_text 
			from documents_read_info
			where article_id = %s
			)
			t4 on t1.owner2 = t4.read_id 
			left join 
			(select read_id 
					,read_text 
			from documents_read_info
			where article_id = %s
			)
			t5 on t1.owner3 = t5.read_id 
			group by owner,read_flag,text_chunk_id
                    '''

        self.cursor.execute(sql, (article_id, article_id, article_id, article_id, article_id, article_id))
        self.conn.commit()

    # 将数据插入表格表
    def insert_data_to_table_info(self,header_row, header_col, text, article_id):
        sql = "INSERT INTO documents_table_info (row_header,col_header,content,article_id) VALUES ( %s, %s,%s, %s) "
        self.cursor.execute(sql, (header_row, header_col, text, article_id))
        self.conn.commit()


# 依次遍历文档内容的函数
def iter_block_items(parent):
    # 判断传入的是否为word文档对象，是则获取文档内容的全部子对象
    if isinstance(parent, dc):
        parent_elm = parent.element.body
    # 判断传入的是否为单元格，是则获取单元格内全部子对象
    elif isinstance(parent, _Cell):
        parent_elm = parent._tc
    else:
        raise ValueError("something's not right")

    # 遍历全部子对象
    for child in parent_elm.iterchildren():
        # 判断是否为段落，是则返回段落对象
        if isinstance(child, CT_P):
            yield Paragraph(child, parent)
        # 判断是否为表格，是则返回表格对象
        if isinstance(child, CT_Tbl):
            yield Table(child, parent)



# 判断段落类型
def get_para_style(para):
    read_flag = 99
    if  para.style.name == 'Title':
        read_flag = 0
    elif para.style.name == 'Heading 3':
        read_flag = 3
    elif para.style.name == 'Heading 2':
        read_flag = 2
    elif para.style.name == 'Heading 1':
        read_flag = 1
    return  read_flag


# 寻找段落父级拥有者
def seek_owner(lis,i):
    if lis[i] == 0 :
        n = -1
    else :
        n = i - 1 
        while True:
            if lis[n] < lis[i]:
                break
            else :
                n=n-1
    if n < -1 :
        n = -1
    return n


def get_table_dataframe(table):
    date = []
    for i, row in enumerate(table.rows):
        text = [cell.text.replace("\n",'') for cell in row.cells]
        date.append(text)
    txt = ""  # pd依赖的DataFrame方法将字典数据转换成列表集
    for i in date:
        txt  = txt + "      ".join(i) +"\n"
    return txt



if __name__ == '__main__':
    from pathlib import Path
    tdb = ToDataBase()
    path = r"D:\file_data\20250208_neibuziliao47_out"
    idx = 1
    flag = 1# 为0的时候是测试文章是否存在问题，为1的时候就是入MySQL库
    for fp in Path(path).iterdir():
        if flag == 0:
            doc = Document(fp)
            lis= []
            read_id = 0
            for block in iter_block_items(doc):
                # print(block.style.name)
                # print(block.text)
                if re.match('(.*)[T,t]able(.*)|网格*',block.style.name):
                    df = get_table_dataframe(block)
                    # print(df)
                    lis.append(100)
                    read_flag = 100
                    read_text = df
                    # print(read_text)
                    owner = seek_owner(lis, read_id)
                    owner1 = seek_owner(lis, owner)
                    owner2 = seek_owner(lis, owner1)
                    owner3 = seek_owner(lis, owner2)
                    read_id += 1
                else:
                    if block.text.strip():
                        # print(block.text.strip())
                        read_text = block.text
                        read_flag = get_para_style(block)
                        lis.append(read_flag)  # [1,99,2,99,3,99,99,2,99,1,]
                        owner = seek_owner(lis, read_id)
                        owner1 = seek_owner(lis, owner)
                        owner2 = seek_owner(lis, owner1)
                        owner3 = seek_owner(lis, owner2)
                        read_id += 1
        else:
            file_path = fp
            article_name = Path(fp).stem
            print(f"第{idx}篇文件名：{article_name}")
            idx= idx+1
            Seq = tdb.is_exists(article_name)
            print(f"文件序号：{Seq}")
            if not Seq:
                lis = []
                read_id = 0

                # doc = Document(os.path.join(path,article_name+".docx"))
                doc = Document(file_path)

                author = doc.core_properties.author
                # print(f"author:{author}")
                submit_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                # print(f"submit_time:{submit_time}")
                file_path = f"/app/create_docx/out_pdf/{article_name}.docx"
                # print(f"file_path:{file_path}")
                # 插入数据到article_info表
                new_article_name = tdb.insert_article(article_name,file_path,author)
                # doc.save(os.path.join("D:\data\docx_new", new_article_name+".docx"))
                # 读取文章id
                article_id = tdb.is_exists(article_name)
                print(f"新文件序号：{article_id}")
                type_block = []
                for block in iter_block_items(doc):
                    # 判断是否是表格
                    if re.match('(.*)[T,t]able(.*)|网格*',block.style.name):
                        print(f"block.style.name:{block.style.name}")

                        df = get_table_dataframe(block)
                        # print(df)
                        lis.append(100)
                        read_flag = 100
                        read_text = df
                        # read_text = str(df)
                        # read_text = df.to_string(index=False,header=True);
                        print(read_text)
                        owner = seek_owner(lis, read_id)
                        owner1 = seek_owner(lis, owner)
                        owner2 = seek_owner(lis, owner1)
                        owner3 = seek_owner(lis, owner2)
                        tdb.insert_data_to_read_info(article_id, read_id, read_text, read_flag, owner,owner1,owner2,owner3)
                        #print(read_id, read_flag, owner, lis)
                        read_id += 1
                        #print(f"df:{df}")
                    else:
                        if block.text.strip():
                            read_text = block.text
                            read_flag = get_para_style(block)
                            lis.append(read_flag)  # [1,99,2,99,3,99,99,2,99,1,]
                            owner = seek_owner(lis, read_id)
                            owner1 = seek_owner(lis, owner)
                            owner2 = seek_owner(lis, owner1)
                            owner3 = seek_owner(lis, owner2)
                            tdb.insert_data_to_read_info(article_id, read_id, read_text, read_flag, owner, owner1, owner2,
                                                         owner3)
                            # print(read_id,read_text,block.style.name, read_flag, owner,owner1,owner2, lis)
                            # print(read_id,read_text, read_flag, owner,owner1,owner2, lis)
                            read_id += 1

                #对导入的文章进行处理，为每个正文段落增加祖先节点
                tdb.insert_data_to_read_info_mid(article_id)




